package com.engine.core.util;

import com.engine.core.domain.GenTable;
import com.engine.core.gen.Mysql;
import com.engine.core.rowMapper.MappedType;
import com.engine.core.rowMapper.MappedTypeRowMapper;
import com.google.common.collect.Maps;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import sun.reflect.generics.reflectiveObjects.TypeVariableImpl;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.function.BiFunction;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JdbcTemplateUtils {

    private static Logger logger= LoggerFactory.getLogger(JdbcTemplateUtils.class);

    private static Map<Class<?>, JdbcType> typeMap = new HashMap<>();

    static {
        //设置默认的类型转换，参考 TypeHandlerRegistry
        register(Boolean.class, JdbcType.BOOLEAN);
        register(boolean.class, JdbcType.BOOLEAN);

        register(Byte.class, JdbcType.TINYINT);
        register(byte.class, JdbcType.TINYINT);

        register(Short.class, JdbcType.SMALLINT);
        register(short.class, JdbcType.SMALLINT);

        register(Integer.class, JdbcType.INTEGER);
        register(int.class, JdbcType.INTEGER);

        register(Long.class, JdbcType.BIGINT);
        register(long.class, JdbcType.BIGINT);

        register(Float.class, JdbcType.FLOAT);
        register(float.class, JdbcType.FLOAT);

        register(Double.class, JdbcType.DOUBLE);
        register(double.class, JdbcType.DOUBLE);

        register(String.class, JdbcType.VARCHAR);

        register(BigDecimal.class, JdbcType.DECIMAL);
        register(BigInteger.class, JdbcType.DECIMAL);

        register(Byte[].class, JdbcType.BLOB);
        register(byte[].class, JdbcType.BLOB);

        register(Date.class, JdbcType.DATE);
        register(java.sql.Date.class, JdbcType.DATE);
        register(Time.class, JdbcType.TIME);
        register(Timestamp.class, JdbcType.TIMESTAMP);

        register(Character.class, JdbcType.CHAR);
        register(char.class, JdbcType.CHAR);
    }


    protected static final TypeHandlerRegistry typeHandlerRegistry = new TypeHandlerRegistry();

    public static <T> List<T> fetchResult(String sql, JdbcTemplate jdbcTemplate, BiFunction<ResultSet,Integer, Result<T>> fn) throws Exception{
        return fetchResult(sql,jdbcTemplate,fn,null);
    }

    public static <T> T fetchResult(String sql,JdbcTemplate jdbcTemplate,Class<T> tClass)throws Exception{
        List<T> datas=fetchResult(sql,jdbcTemplate,(rs,idx)->{
            try {
               T t=typeHandlerRegistry.getTypeHandler(tClass).getResult(rs,idx);
               return new Result<>(t);
            }catch (SQLException e){
                return new Result<>(e);
            }
        },null);
        if(datas!=null && datas.size()>0){
           return datas.get(0);
        }else{
            return null;
        }
    }


    /**
       @param sql 后台拼接，或者前台传过来的sql
       @param jdbcTemplate 执行器
       @param formatMap 前台传过来，用于格式化数据
       @param typeFormat 后台配置文件，通过java.sql.Types 数据类型进行格式化数据
       @param queryVar 查询参数
     */
    public static List<Map<String,Object>> fetchResult(String sql, JdbcTemplate jdbcTemplate, Map<String, MappedType> formatMap, Map<Integer,String> typeFormat, Object queryVar) throws Exception{
         NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);
        List<Map<String, Object>> returnData=null;
         if(queryVar!=null && queryVar.getClass().isArray()){
            returnData=jdbcTemplate.query(sql, (Object[])queryVar,new MappedTypeRowMapper(typeFormat,formatMap));
        }else if(Map.class.isInstance(queryVar)){
            returnData=namedParameterJdbcTemplate.query(sql,(Map<String,?>)queryVar,new MappedTypeRowMapper(typeFormat,formatMap));
        }else{
            returnData=jdbcTemplate.query(sql,new MappedTypeRowMapper(typeFormat,formatMap));
        }
         return returnData;
    }


    public static <T> List<T> fetchResult(String sql, JdbcTemplate jdbcTemplate, BiFunction<ResultSet,Integer, Result<T>> fn, Object ...queryVar) throws Exception{
        List<T> returnData=null;
        if(queryVar!=null){
           returnData=jdbcTemplate.query(sql, queryVar, new RowMapper<T>() {
                @Override
                public T mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Result<T> result=fn.apply(rs,rowNum);
                    if(!result.success()){
                           throw new SQLException(result.getExp());
                    }else{
                          return result.get();
                    }
                }
            });
        }else{
            returnData=jdbcTemplate.query(sql, queryVar, new RowMapper<T>() {
                @Override
                public T mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Result<T> result=fn.apply(rs,rowNum);
                    if(!result.success()){
                        throw new SQLException(result.getExp());
                    }else{
                        return result.get();
                    }
                }
            });
        }
        return returnData;
    }

    public static <T> List<T> fetchArrayResultByTypeHandler(String sql, JdbcTemplate jdbcTemplate,Class<T> pojoClass, Object ...queryVar) throws Exception{
        return fetchResultByTypeHandler(sql,jdbcTemplate,pojoClass,queryVar);
    }

    public static <T> List<T> fetchMapResultByTypeHandler(String sql, JdbcTemplate jdbcTemplate,Class<T> pojoClass, Map<String,Object> paramMap) throws Exception{
        return fetchResultByTypeHandler(sql,jdbcTemplate,pojoClass,paramMap);
    }

    public static <T> List<T> fetchResultByTypeHandler(String sql, JdbcTemplate jdbcTemplate,Class<T> pojoClass, Object queryVar) throws Exception{
        T t=pojoClass.newInstance();
        MetaObject metaObject= SystemMetaObject.forObject(t);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);

        RowMapper<T>  typeHandlerMapper=new RowMapper<T>() {
            @Override
            public T mapRow(ResultSet rs, int rowNum) throws SQLException {
                T t =null;
                try {
                    t = pojoClass.newInstance();
                }catch (Exception e){
                    throw  new SQLException("can not instance for class:"+pojoClass.getName(),e);
                }
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                for(int a=1;a<=columnCount;a++ ) {
                    String label = metaData.getColumnLabel(a);
                    int stype = metaData.getColumnType(a);
                    if(metaObject.hasSetter(getPropName(label))){
                        Class cz = metaObject.getSetterType(getPropName(label));
                        Object op=typeHandlerRegistry.getTypeHandler(cz).getResult(rs,label);
                        PropertyDescriptor propertyDescriptor= BeanUtils.getPropertyDescriptor(pojoClass,getPropName(label));
                        try {
                            if(!String.valueOf(op).equalsIgnoreCase("null")){
                                propertyDescriptor.getWriteMethod().invoke(t,op);
                            }else{
                                propertyDescriptor.getWriteMethod().invoke(t,new Object[]{null});
                            }
                        } catch (Exception e) {
                            throw new SQLException("setMethod invoke fail value:"+String.valueOf(t)+",prop:"+label,e);
                        }
                    }
                }
                return t;
            }
        };

        List<T> returnData=null;
        if(queryVar!=null && queryVar.getClass().isArray()){
            returnData=jdbcTemplate.query(sql, (Object[])queryVar,typeHandlerMapper);
        }else if(Map.class.isInstance(queryVar)){
            returnData=namedParameterJdbcTemplate.query(sql,(Map<String,?>)queryVar,typeHandlerMapper);
        }else{
            returnData=jdbcTemplate.query(sql,typeHandlerMapper);
        }
        return returnData;
    }

    public static <T> int[] insertDataByTypeHandler(String tableName,JdbcTemplate jdbcTemplate,List<T> data,Map<String,String> others,Set<String> noneColumns){
         return insertDataByTypeHandlerAndInsertFunction(tableName,jdbcTemplate,data,others,noneColumns,null,null);
    }

    public static <T> int[] insertDataByTypeHandler(String tableName,JdbcTemplate jdbcTemplate,List<T> data,Map<String,String> others,String ... noneColumns){
        return insertDataByTypeHandlerAndInsertFunction(tableName,jdbcTemplate,data,others,new HashSet<>(Arrays.asList(noneColumns)),null,null);
    }

    public static <T> int[] insertDataByTypeHandler(String tableName,JdbcTemplate jdbcTemplate,T data,Map<String,String> others,String ... noneColumns){
        return insertDataByTypeHandlerAndInsertFunction(tableName,jdbcTemplate,Arrays.asList(data),others,new HashSet<>(Arrays.asList(noneColumns)),null,null);
    }


    public static <T> int[] insertDataByAndInsertFunction(String tableName,JdbcTemplate jdbcTemplate,List<T> data,Map<String,String> others,Set<String> noneColumns,BiFunction<String,Object,Boolean> isInsertFunction){
        return insertDataByTypeHandlerAndInsertFunction(tableName,jdbcTemplate,data,others,noneColumns,null,isInsertFunction);
    }


    /**
     * 单条数据插入，返回主键
     * @param tableName 表名
     * @param jdbcTemplate 语句执行器
     * @param data 数据
     * @param columnsTypeMap 插入类型
     * @param others 其他sql插入项
     * @return
     */
    public static  Response insertOneDataByColumns(String tableName,JdbcTemplate jdbcTemplate,Map<String,Object> data,
                                                                     TreeMap<String,MappedType> columnsTypeMap,Map<String,String> others){
        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder("insert into "+tableName+" ( ");
        StringBuilder valuesBuilder=new StringBuilder(" values(");


        Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
        LinkedHashMap<String,int[]> propNameJdbcType=new LinkedHashMap<>();
        LinkedHashMap<String,String> propNameJdbcTypeStr=new LinkedHashMap<>();

        Iterator<Map.Entry<String, MappedType>> columnsTypeMapIterator= columnsTypeMap.entrySet().iterator();

        int idx=1;
        while (columnsTypeMapIterator.hasNext()) {
            Map.Entry<String, MappedType> columnsTypeMapEntry=columnsTypeMapIterator.next();
            MappedType mappedType=columnsTypeMapEntry.getValue();
            String tableColumnName=columnsTypeMapEntry.getKey();
            if(others.containsKey(tableColumnName)){
                continue;
            }
            sqlBuilder.append(tableColumnName);
            sqlBuilder.append(",");

            valuesBuilder.append("?");
            valuesBuilder.append(",");
            propNameJdbcType.put(tableColumnName,new int[]{idx++,typeMap.get(mappedType.getTypeClass()).TYPE_CODE});
            propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());
        }

        while(othersIterator.hasNext()){
            Map.Entry<String, String>  othersEntry=othersIterator.next();
            valuesBuilder.append(othersEntry.getValue());
            valuesBuilder.append(",");

            sqlBuilder.append(othersEntry.getKey());
            sqlBuilder.append(",");
        }

        if(sqlBuilder.length()>1){
            sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        }

        if(valuesBuilder.length()>1){
            valuesBuilder.deleteCharAt(valuesBuilder.length()-1);
        }

        String sql=sqlBuilder.toString()+") "+valuesBuilder.toString()+" )";
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,propNameJdbcTypeStr);


        KeyHolder keyHolder = null;
        boolean failNum= false;
        try {
            keyHolder = new GeneratedKeyHolder();
            PreparedStatementCreator preparedStatementCreator = con -> {
                PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                Iterator<Map.Entry<String, int[]>>  propNameJdbcTypeIterator=propNameJdbcType.entrySet().iterator();
                while(propNameJdbcTypeIterator.hasNext()){
                    Map.Entry<String, int[]> propNameJdbcTypeEntry=propNameJdbcTypeIterator.next();
                    int paramIdx=propNameJdbcTypeEntry.getValue()[0];
                    int targetSqlType=propNameJdbcTypeEntry.getValue()[1];
                    Object o=data.get(propNameJdbcTypeEntry.getKey());
                    ps.setObject(paramIdx,o,targetSqlType);
                }
                return ps;
            };
            int results=jdbcTemplate.update(preparedStatementCreator,keyHolder);
            failNum = results==1;
            response.setSuccess(failNum);
            response.set("rows",results);
        } catch (Exception e) {
            response.setErr(e);
        }
        if(!failNum){
            response.set("key",keyHolder.getKey());
        }
        return response;
    }

    public static  Response deleteOneDataById(GenTable genTable, JdbcTemplate jdbcTemplate, Object ids,
                                                   LinkedHashMap<String,MappedType> columnsTypeMap, Map<String,String> others){
        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder("delete from "+genTable.getResourceName()+"  ");
        StringBuilder whereBuilder=new StringBuilder(" where ");
        Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
        List<String> whereColumns=new ArrayList<>();
        List<String[]> otherValues=new ArrayList<>();
        while(othersIterator.hasNext()){
            Map.Entry<String, String>  othersEntry=othersIterator.next();
            otherValues.add(new String[]{othersEntry.getKey(),othersEntry.getValue()});
            whereBuilder.append(othersEntry.getKey());
            whereBuilder.append("=");
            whereBuilder.append(othersEntry.getValue());
            whereBuilder.append(" and ");
        }
        whereBuilder.append(genTable.getPkColumn().getColumnName());
        if (ids.getClass().isArray()) {
            whereBuilder.append(" in (");
            int len=java.lang.reflect.Array.getLength(ids);
            for (int i = 0; i < len; i++) {
                whereBuilder.append("?,");
            }
            whereBuilder.deleteCharAt(whereBuilder.length()-1);
            whereBuilder.append(")");
        }else{
            whereBuilder.append("=");
            whereBuilder.append("?");
        }
        String sql=sqlBuilder.toString()+" "+whereBuilder.toString()+" ";

        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,genTable.getPkColumn().getColumnType());

        PreparedStatementCreator preparedStatementCreator = con -> {
            PreparedStatement ps = con.prepareStatement(sql);
            int targetSqlType=typeMap.get(columnsTypeMap.get(genTable.getPkColumn().getColumnName()).getTypeClass()).TYPE_CODE;
            MappedType mappedType=columnsTypeMap.get(genTable.getPkColumn().getColumnName());
            if(ids.getClass().isArray()){
                int len=java.lang.reflect.Array.getLength(ids);
                int idx=1;
                for (int i = 0; i <len ; i++) {
                    targetSqlType=Mysql.getUpdateSqlType(ids,targetSqlType,mappedType);
                    ps.setObject(idx,java.lang.reflect.Array.get(ids,i),targetSqlType);
                    idx++;
                }
            }else{
                targetSqlType=Mysql.getUpdateSqlType(ids,targetSqlType,mappedType);
                ps.setObject(1,ids,targetSqlType);
            }
            return ps;
        };
        int nums=jdbcTemplate.update(preparedStatementCreator);
        response.setSuccess(nums>0);
        return response;
    }


    public static  Response deleteDataByWhere(GenTable genTable, JdbcTemplate jdbcTemplate, List<Map<String,Object>> data,
                                              LinkedHashMap<String,MappedType> columnsTypeMap, Map<String,String> others){
            Response response=Response.static_success("执行成功!");
            StringBuilder sqlBuilder=new StringBuilder("delete from "+genTable.getResourceName()+"  ");
            StringBuilder whereBuilder=new StringBuilder(" where ");
            Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
            List<String> whereColumns=new ArrayList<>();
            List<String[]> otherValues=new ArrayList<>();
            while(othersIterator.hasNext()){
                Map.Entry<String, String>  othersEntry=othersIterator.next();
                otherValues.add(new String[]{othersEntry.getKey(),othersEntry.getValue()});
                whereBuilder.append(othersEntry.getKey());
                whereBuilder.append("=");
                whereBuilder.append(othersEntry.getValue());
                whereBuilder.append(" and ");
            }
            Iterator<Map.Entry<String,MappedType>>  columnsTypeMapEntryIterator=columnsTypeMap.entrySet().iterator();
            int setIdx=1;
            Map<String,int[]> propNameJdbcType=new LinkedHashMap<>();
            Map<String,String> propNameJdbcTypeStr=new LinkedHashMap<>();
            while (columnsTypeMapEntryIterator.hasNext()) {
                Map.Entry<String,MappedType> columnsTypeMapEntry=columnsTypeMapEntryIterator.next();
                othersIterator=others.entrySet().iterator();
                boolean change=false;
                String tableColumnName=columnsTypeMapEntry.getKey();
                while(othersIterator.hasNext()){
                    Map.Entry<String, String>  othersEntry=othersIterator.next();
                    if(othersEntry.getKey().equalsIgnoreCase(tableColumnName)){
                        change=true;
                        break;
                    }
                }
                if(change){
                    continue;
                }
                whereBuilder.append(tableColumnName);
                whereBuilder.append("=");
                whereBuilder.append("?");
                whereBuilder.append(" and ");
                MappedType mappedType=columnsTypeMapEntry.getValue();
                propNameJdbcType.put(tableColumnName,new int[]{setIdx++,typeMap.get(columnsTypeMap.get(tableColumnName).getTypeClass()).TYPE_CODE});
                propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());
            }
        whereBuilder.append(" 1=1 ");
        String sql=sqlBuilder.toString()+" "+whereBuilder.toString();
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,propNameJdbcTypeStr);
        int[] results=jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter(){
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Iterator<Map.Entry<String, int[]>>  propNameJdbcTypeIterator=propNameJdbcType.entrySet().iterator();
                while(propNameJdbcTypeIterator.hasNext()){
                    Map.Entry<String, int[]> propNameJdbcTypeEntry=propNameJdbcTypeIterator.next();
                    int paramIdx=propNameJdbcTypeEntry.getValue()[0];
                    int targetSqlType=propNameJdbcTypeEntry.getValue()[1];
                    MappedType mappedType=columnsTypeMap.get(propNameJdbcTypeEntry.getKey());
                    Object o=data.get(i).get(propNameJdbcTypeEntry.getKey());
                    targetSqlType=Mysql.getUpdateSqlType(o,targetSqlType,mappedType);
                    ps.setObject(paramIdx,o,targetSqlType);
                }
            }
            @Override
            public int getBatchSize() {
                return data.size();
            }
        });
        boolean failNum= Arrays.stream(results).filter(s->s==0).findFirst().isPresent();
        response.setSuccess(!failNum);
        response.set("rows",results);
        return response;
    }

    public static  Response updateOneDataByColumns(GenTable genTable, JdbcTemplate jdbcTemplate, Map<String,Object> data,
                                                   TreeMap<String,MappedType> columnsTypeMap, Map<String,String> others){

        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder("update "+genTable.getResourceName()+" set ");
        StringBuilder whereBuilder=new StringBuilder(" where ");

        List<String> whereColumns=new ArrayList<>();
        List<String[]> otherValues=new ArrayList<>();
        List<Object>  values=new ArrayList<>();
        Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
        while(othersIterator.hasNext()){
            Map.Entry<String, String>  othersEntry=othersIterator.next();
            otherValues.add(new String[]{othersEntry.getKey(),othersEntry.getValue()});

            sqlBuilder.append(othersEntry.getKey());
            sqlBuilder.append("=");
            sqlBuilder.append(othersEntry.getValue());
            sqlBuilder.append(",");
        }

        Iterator<Map.Entry<String,MappedType>>  columnsTypeMapEntryIterator=columnsTypeMap.entrySet().iterator();

        int setIdx=1;
        int whereIdx=1;
        Map<String,int[]> propNameJdbcType=new LinkedHashMap<>();
        Map<String,String> propNameJdbcTypeStr=new LinkedHashMap<>();
        while (columnsTypeMapEntryIterator.hasNext()) {
            Map.Entry<String,MappedType> columnsTypeMapEntry=columnsTypeMapEntryIterator.next();
            othersIterator=others.entrySet().iterator();
            boolean change=false;
            String tableColumnName=columnsTypeMapEntry.getKey();
            while(othersIterator.hasNext()){
                Map.Entry<String, String>  othersEntry=othersIterator.next();
                if(othersEntry.getKey().equalsIgnoreCase(tableColumnName)){
                    change=true;
                    break;
                }
            }
            if(change){
                continue;
            }
            if(tableColumnName.equals(genTable.getPkColumn().getColumnName())){
                continue;
            }
            sqlBuilder.append(tableColumnName);
            sqlBuilder.append("=");
            sqlBuilder.append("?");
            sqlBuilder.append(",");
            MappedType mappedType=columnsTypeMapEntry.getValue();

            propNameJdbcType.put(tableColumnName,new int[]{setIdx++,typeMap.get(columnsTypeMap.get(tableColumnName).getTypeClass()).TYPE_CODE});
            propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());
        }

        whereBuilder.append(genTable.getPkColumn().getColumnName());
        whereBuilder.append("=");
        whereBuilder.append("?");


        propNameJdbcType.put(genTable.getPkColumn().getColumnName(),new int[]{setIdx++,typeMap.get(columnsTypeMap.get(genTable.getPkColumn().getColumnName()).getTypeClass()).TYPE_CODE});
        propNameJdbcTypeStr.put(genTable.getPkColumn().getColumnName(),typeMap.get(columnsTypeMap.get(genTable.getPkColumn().getColumnName()).getTypeClass()).name());


        if(sqlBuilder.length()>1){
            sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        }

        String sql=sqlBuilder.toString()+" "+whereBuilder.toString();
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,propNameJdbcTypeStr);

        PreparedStatementCreator preparedStatementCreator = con -> {
            PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            Iterator<Map.Entry<String, int[]>>  propNameJdbcTypeIterator=propNameJdbcType.entrySet().iterator();
            while(propNameJdbcTypeIterator.hasNext()){
                Map.Entry<String, int[]> propNameJdbcTypeEntry=propNameJdbcTypeIterator.next();
                int paramIdx=propNameJdbcTypeEntry.getValue()[0];
                int targetSqlType=propNameJdbcTypeEntry.getValue()[1];
                Object o=data.get(propNameJdbcTypeEntry.getKey());
                ps.setObject(paramIdx,o,targetSqlType);
            }
            return ps;
        };
        int num=jdbcTemplate.update(preparedStatementCreator);
        response.setSuccess(num>0);
        response.set("rows",num);
        return response;
    }

    public static  Response updateMultiData(GenTable genTable,JdbcTemplate jdbcTemplate,List<Map<String,Object>> data,
                                            LinkedHashMap<String,MappedType> columnsTypeMap,Map<String,String> others,
                                            LinkedHashMap<String, MappedType> whereColumns,Map<String,String> whereOthersMap,boolean isbatch){
        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder("update "+genTable.getResourceName()+" set ");
        StringBuilder whereBuilder=new StringBuilder(" where ");

        List<String[]> otherValues=new ArrayList<>();
        List<Object>  values=new ArrayList<>();


        Iterator<Map.Entry<String,MappedType>>  updateColumnsTypeMapEntryIterator=columnsTypeMap.entrySet().stream()
                .iterator();


        int setIdx=1;
        int whereIdx=1;
        Map<String,Integer> propNameJdbcType=new LinkedHashMap<>();
        Map<String,String> propNameJdbcTypeStr=new LinkedHashMap<>();
        while (updateColumnsTypeMapEntryIterator.hasNext()) {
            Map.Entry<String,MappedType> columnsTypeMapEntry=updateColumnsTypeMapEntryIterator.next();
            boolean change=false;
            String tableColumnName=columnsTypeMapEntry.getKey();
            sqlBuilder.append(tableColumnName);
            sqlBuilder.append("=");
            if (others.containsKey(tableColumnName)) {
                    sqlBuilder.append(others.get(tableColumnName));
            }else{
                MappedType mappedType=columnsTypeMapEntry.getValue();
                propNameJdbcType.put(tableColumnName,typeMap.get(columnsTypeMap.get(tableColumnName).getTypeClass()).TYPE_CODE);
                propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());

                sqlBuilder.append(":"+tableColumnName);
            }
            sqlBuilder.append(",");
        }

        Iterator<Map.Entry<String,MappedType>>  whereColumnsTypeMapEntryIterator=whereColumns.entrySet().stream().iterator();
        while (whereColumnsTypeMapEntryIterator.hasNext()) {
            Map.Entry<String,MappedType> whereColumnsTypeMapEntry=whereColumnsTypeMapEntryIterator.next();
            boolean change=false;
            String tableColumnName=whereColumnsTypeMapEntry.getKey();
            whereBuilder.append(tableColumnName);
            whereBuilder.append("=");
            if (whereOthersMap.containsKey(tableColumnName)) {
                whereBuilder.append(whereOthersMap.get(tableColumnName));
            }else{
                MappedType mappedType=whereColumnsTypeMapEntry.getValue();
                propNameJdbcType.put(tableColumnName,typeMap.get(whereColumns.get(tableColumnName).getTypeClass()).TYPE_CODE);
                propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());
                whereBuilder.append(":"+tableColumnName);
            }
            whereBuilder.append(",");
        }

        if(sqlBuilder.length()>1){
            sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        }

        if(whereBuilder.length()>1){
            whereBuilder.deleteCharAt(whereBuilder.length()-1);
        }

        String sql=sqlBuilder.toString()+" "+whereBuilder.toString();
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,propNameJdbcTypeStr);


        NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);

        if(!isbatch){
            boolean failNum= false;
            int idx=0;
            int results[]=new int[data.size()];
            Number keys[]=new Number[data.size()];
            for (Map<String,Object> map:data) {
                MapSqlParameterSource sqlParameterSources=newMapSqlParameterSource(map, propNameJdbcType);
                results[idx]=namedParameterJdbcTemplate.update(sql,sqlParameterSources);
                idx++;
            }
            failNum= Arrays.stream(results).filter(s->s==0).findFirst().isPresent();
            response.setSuccess(!failNum);
            response.set("rows",results);
        }else{
            int idx=0;
            int results[]=new int[data.size()];
            boolean failNum= false;
            MapSqlParameterSource[]  mapSqlParameterSourceArr=new MapSqlParameterSource[data.size()];
            for (Map<String,Object> map:data) {
                mapSqlParameterSourceArr[idx]=newMapSqlParameterSource(map, propNameJdbcType);
                idx++;
            }
            results= namedParameterJdbcTemplate.batchUpdate(sql,mapSqlParameterSourceArr);
            failNum= Arrays.stream(results).filter(s->s==0).findFirst().isPresent();
            response.setSuccess(!failNum);
            response.set("rows",results);
        }

        return response;
    }

    /**
     * 多行数据插入，不返回数据库主键
     * @param tableName
     * @param jdbcTemplate
     * @param data
     * @param columnsTypeMap
     * @param others
     * @return
     */
    public static  Response insertMultiDataBatch(String tableName,JdbcTemplate jdbcTemplate,List<Map<String,Object>> data,
                                                     LinkedHashMap<String,MappedType> columnsTypeMap,Map<String,String> others){

        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder("insert into "+tableName+" ( ");
        StringBuilder valuesBuilder=new StringBuilder(" values(");


        Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
        Map<String,Integer> propNameJdbcType=new TreeMap<>();
        Map<String,String> propNameJdbcTypeStr=new TreeMap<>();

        Iterator<Map.Entry<String, MappedType>> columnsTypeMapIterator= columnsTypeMap.entrySet().iterator();

        int idx=1;
        while (columnsTypeMapIterator.hasNext()) {
            Map.Entry<String, MappedType> columnsTypeMapEntry=columnsTypeMapIterator.next();
            MappedType mappedType=columnsTypeMapEntry.getValue();
            String tableColumnName=columnsTypeMapEntry.getKey();
            if(others.containsKey(tableColumnName)){
                continue;
            }
            sqlBuilder.append(tableColumnName);
            sqlBuilder.append(",");

            valuesBuilder.append(":"+tableColumnName);
            valuesBuilder.append(",");
            propNameJdbcType.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).TYPE_CODE);
            propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());
        }

        while(othersIterator.hasNext()){
            Map.Entry<String, String>  othersEntry=othersIterator.next();
            valuesBuilder.append(othersEntry.getValue());
            valuesBuilder.append(",");

            sqlBuilder.append(othersEntry.getKey());
            sqlBuilder.append(",");
        }

        if(sqlBuilder.length()>1){
            sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        }

        if(valuesBuilder.length()>1){
            valuesBuilder.deleteCharAt(valuesBuilder.length()-1);
        }

        String sql=sqlBuilder.toString()+") "+valuesBuilder.toString()+" )";
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,propNameJdbcTypeStr);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);
        SqlParameterSource[] batch=new SqlParameterSource[data.size()];
        for(int i=0;i<data.size();i++){
            batch[i]=newMapSqlParameterSource(data.get(i), propNameJdbcType);
        }

        int[] results=namedParameterJdbcTemplate.batchUpdate(sql,batch);
        boolean failNum= Arrays.stream(results).filter(s->s==0).findFirst().isPresent();
        response.setSuccess(!failNum);
        response.set("rows",results);
        return response;
    }

    public static  Response insertMultiData(String tableName,JdbcTemplate jdbcTemplate,List<Map<String,Object>> data,
                                                 LinkedHashMap<String,MappedType> columnsTypeMap,Map<String,String> others){

        Response response=Response.static_success("执行成功!");
        StringBuilder sqlBuilder=new StringBuilder("insert into "+tableName+" ( ");
        StringBuilder valuesBuilder=new StringBuilder(" values(");


        Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
        Map<String,Integer> propNameJdbcType=new TreeMap<>();
        Map<String,String> propNameJdbcTypeStr=new TreeMap<>();

        Iterator<Map.Entry<String, MappedType>> columnsTypeMapIterator= columnsTypeMap.entrySet().iterator();

        int idx=1;
        while (columnsTypeMapIterator.hasNext()) {
            Map.Entry<String, MappedType> columnsTypeMapEntry=columnsTypeMapIterator.next();
            MappedType mappedType=columnsTypeMapEntry.getValue();
            String tableColumnName=columnsTypeMapEntry.getKey();
            if(others.containsKey(tableColumnName)){
                continue;
            }
            sqlBuilder.append(tableColumnName);
            sqlBuilder.append(",");

            valuesBuilder.append(":"+tableColumnName);
            valuesBuilder.append(",");
            propNameJdbcType.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).TYPE_CODE);
            propNameJdbcTypeStr.put(tableColumnName,typeMap.get(mappedType.getTypeClass()).name());
        }

        while(othersIterator.hasNext()){
            Map.Entry<String, String>  othersEntry=othersIterator.next();
            valuesBuilder.append(othersEntry.getValue());
            valuesBuilder.append(",");

            sqlBuilder.append(othersEntry.getKey());
            sqlBuilder.append(",");
        }

        if(sqlBuilder.length()>1){
            sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        }

        if(valuesBuilder.length()>1){
            valuesBuilder.deleteCharAt(valuesBuilder.length()-1);
        }

        String sql=sqlBuilder.toString()+") "+valuesBuilder.toString()+" )";
        response.set(QueryUtil.QUERY_RESULT_SQL,sql);
        response.set(QueryUtil.QUERY_RESULT_COLUMN_TYPE,propNameJdbcTypeStr);

        NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate);

        KeyHolder keyHolder = null;
        boolean failNum= false;
        idx=0;
        int results[]=new int[data.size()];
        Number keys[]=new Number[data.size()];
        for (Map<String,Object> map:data) {
            MapSqlParameterSource sqlParameterSources=newMapSqlParameterSource(map, propNameJdbcType);
            keyHolder = new GeneratedKeyHolder();
            results[idx]=namedParameterJdbcTemplate.update(sql,sqlParameterSources,keyHolder);
            keys[idx]=keyHolder.getKey();
            idx++;
        }
        failNum= Arrays.stream(results).filter(s->s==0).findFirst().isPresent() || results.length<data.size();
        response.setSuccess(!failNum);
        response.set("rows",results);
        response.set("keys",keys);
        return response;
    }

    public static <T> int[] insertDataByTypeHandlerAndInsertFunction(String tableName,JdbcTemplate jdbcTemplate,List<T> data,Map<String,String> others,
                                                    Set<String> noneColumns,
                                                    Map<String,JdbcType> jdbcTypeMap,
                                                    BiFunction<String,Object,Boolean> isInsertFunction){
            List<String[]> otherValues=new ArrayList<>();
            List<Object>  values=new ArrayList<>();
            Map<String,int[]> propNameJdbcType=new TreeMap<>();

            String sqlStructureInfo="select * from "+tableName+" where 1<>1";
            String[] tableColumns=fetchTableColumns(sqlStructureInfo,jdbcTemplate);

            others=others==null?new HashMap<>():others;
            noneColumns=noneColumns==null?new HashSet<>():noneColumns;
            jdbcTypeMap=jdbcTypeMap==null?new HashMap<>():jdbcTypeMap;

            StringBuilder sqlBuilder=new StringBuilder("insert into "+tableName+"(");
            StringBuilder valuesBuilder=new StringBuilder(" values(");
            Iterator<Map.Entry<String, String>>  othersIterator=others.entrySet().iterator();
            while(othersIterator.hasNext()){
                Map.Entry<String, String>  othersEntry=othersIterator.next();
                otherValues.add(new String[]{othersEntry.getKey(),othersEntry.getValue()});

                valuesBuilder.append(othersEntry.getValue());
                valuesBuilder.append(",");

                sqlBuilder.append(othersEntry.getKey());
                sqlBuilder.append(",");
            }

            T tp=data.get(0);
            MetaObject metaObject= SystemMetaObject.forObject(tp);
            String[] getterNames=metaObject.getGetterNames();
            int idx=1;
            for (int i = 0; i < tableColumns.length; i++) {
                othersIterator=others.entrySet().iterator();
                boolean change=false;
                String tableColumnName=tableColumns[i];
                while(othersIterator.hasNext()){
                    Map.Entry<String, String>  othersEntry=othersIterator.next();
                    if(othersEntry.getKey().equalsIgnoreCase(tableColumnName)){
                        change=true;
                        break;
                    }
                }
                if(change){
                    continue;
                }
                if(noneColumns.contains(tableColumnName)){
                    continue;
                }
                if(!metaObject.hasGetter(getPropName(tableColumnName))){
                    continue;
                }

                sqlBuilder.append(tableColumns[i]);
                sqlBuilder.append(",");

                valuesBuilder.append("?");
                valuesBuilder.append(",");
                if(jdbcTypeMap.containsKey(getPropName(tableColumnName))){
                    propNameJdbcType.put(getPropName(tableColumnName),new int[]{idx++,typeMap.get(jdbcTypeMap.get(getPropName(tableColumnName))).TYPE_CODE});
                }else{
                    propNameJdbcType.put(getPropName(tableColumnName),new int[]{idx++,typeMap.get(metaObject.getGetterType(getPropName(tableColumnName))).TYPE_CODE});
                }
            }

            if(sqlBuilder.charAt(sqlBuilder.length()-1)==','){
                sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
            }

            if(valuesBuilder.charAt(valuesBuilder.length()-1)==','){
                valuesBuilder.deleteCharAt(valuesBuilder.length()-1);
            }
            valuesBuilder.append(")");
            sqlBuilder.append(")");

            sqlBuilder.append(valuesBuilder);


         logger.info("excute sql is:"+sqlBuilder.toString());

         propNameJdbcType.entrySet().forEach(s->{
             logger.info("type mapping fieldName:"+s.getKey()+",TYPE_CODE:"+s.getValue()[1]);
         });

         int[] results=jdbcTemplate.batchUpdate(sqlBuilder.toString(),new BatchPreparedStatementSetter(){
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Iterator<Map.Entry<String, int[]>>  propNameJdbcTypeIterator=propNameJdbcType.entrySet().iterator();
                    MetaObject metaObject= SystemMetaObject.forObject(data.get(i));
                    while(propNameJdbcTypeIterator.hasNext()){
                        Map.Entry<String, int[]> propNameJdbcTypeEntry=propNameJdbcTypeIterator.next();
                        Object o=metaObject.getValue(propNameJdbcTypeEntry.getKey());
                        if(isInsertFunction!=null && !isInsertFunction.apply(propNameJdbcTypeEntry.getKey(),o)){
                            ps.setNull(propNameJdbcTypeEntry.getValue()[0],propNameJdbcTypeEntry.getValue()[1]);
                        }else{
                            ps.setObject(propNameJdbcTypeEntry.getValue()[0],o,propNameJdbcTypeEntry.getValue()[1]);
                        }
                    }
                }
                @Override
                public int getBatchSize() {
                    return data.size();
                }
         });
         return results;
    }



    public static <T> T executeDBFunction(JdbcTemplate jdbcTemplate,String functionName,Class<T> classz,Object... vars){
       return  jdbcTemplate.execute(new CallableStatementCreator(){
            @Override
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                StringBuilder storedProcBuilder=new StringBuilder("{? = call ");
                storedProcBuilder.append(functionName);
                if(vars.length>0){
                    storedProcBuilder.append(" (");
                    for (Object o:vars) {
                        storedProcBuilder.append("?");
                        storedProcBuilder.append(",");
                    }
                    if(storedProcBuilder.charAt(storedProcBuilder.length()-1)==','){
                        storedProcBuilder.deleteCharAt(storedProcBuilder.length()-1);
                    }
                    storedProcBuilder.append(")}");
                }else{
                    storedProcBuilder.append(" }");
                }
                CallableStatement cs = con.prepareCall(storedProcBuilder.toString());
                cs.registerOutParameter(1,typeMap.get(classz).TYPE_CODE);
                int idx=2;
                for (Object o:vars) {
                    cs.setObject(idx++,o,typeMap.get(o.getClass()).TYPE_CODE);
                }
                cs.execute();
                return cs;
            }
        },new CallableStatementCallback<T>(){
            @Override
            public T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                return cs.getObject(1,classz);
            }
        });
    }


    /**
     * @author wangj
     * @description //TODO
     * @date 2021/3/29
     *  执行存储过程，并返回执行结果
     *
     * @param jdbcTemplate
     * @param functionName 存储过程名称
     * @param existsOutParams  如果是输出参数，需要指定输出参数名称
     * @param classz  输出参数类型名称,输出参数类型
     * @param resultSetOutParams 如果是游标输出结果,需要指定输出参数名称
     * @param vars 输入参数
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     **/
    public static List<Map<String,Object>> executeDBProcedure(JdbcTemplate jdbcTemplate,String functionName,final String existsOutParams[],Class<?> classz[],
                                                              final String[] resultSetOutParams,Object... vars){
        return jdbcTemplate.execute(new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection connection) throws SQLException {
                StringBuilder storedProcBuilder = new StringBuilder("{call ");
                storedProcBuilder.append(functionName);
                if (vars.length > 0) {
                    storedProcBuilder.append(" (");
                    for (Object o : vars) {
                        storedProcBuilder.append("?");
                        storedProcBuilder.append(",");
                    }
                    if (storedProcBuilder.charAt(storedProcBuilder.length() - 1) == ',') {
                        storedProcBuilder.deleteCharAt(storedProcBuilder.length() - 1);
                    }
                    storedProcBuilder.append(")}");
                } else {
                    storedProcBuilder.append(" }");
                }
                CallableStatement cs = connection.prepareCall(storedProcBuilder.toString());
                if(existsOutParams!=null && existsOutParams.length>0){
                    for (int i = 1; i <= existsOutParams.length; i++) {
                        cs.registerOutParameter(i,typeMap.get(classz[i-1]).TYPE_CODE);
                    }
                }
                if(vars.length>0){
                    int idx=1;
                    for (Object o:vars) {
                        cs.setObject(idx++,o,typeMap.get(o.getClass()).TYPE_CODE);
                    }
                }
                cs.execute();
                return cs;
            }
        }, new CallableStatementCallback<List<Map<String,Object>>>() {
            @Override
            public List<Map<String,Object>> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                Map<String,Object> map=new HashMap<>();
                List<Map<String,Object>> list=new ArrayList<>();
                if(existsOutParams!=null && existsOutParams.length>0){
                    for(int a = 1; a <= existsOutParams.length; a++){
                        if(classz!=null){
                            Object o=cs.getObject(resultSetOutParams[a-1],classz[a-1]);
                            map.put(resultSetOutParams[a-1],o);
                        }else{
                            Object o=cs.getObject(resultSetOutParams[a-1]);
                            map.put(resultSetOutParams[a-1],o);
                        }
                    }
                    list.add(map);
                    return list;
                }

                if(resultSetOutParams!=null && resultSetOutParams.length>0){
                    ResultSet rs=cs.getResultSet();
                    while(rs.next()){
                        for(int a = 1; a <= resultSetOutParams.length; a++){
                            if(classz!=null){
                                Object o=rs.getObject(resultSetOutParams[a-1],classz[a-1]);
                                map.put(resultSetOutParams[a-1],o);
                            }else{
                                Object o=rs.getObject(resultSetOutParams[a-1]);
                                map.put(resultSetOutParams[a-1],o);
                            }

                        }
                        list.add(map);
                    }
                }
                return list;
            }
        });
    }


    public  static  Function<ResultSet,Result<Map<String,Object>>> commonResultSetToMap=(rs) ->{
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            Map<String,Object> dataMap=new HashMap();
            for(int a=1;a<=columnCount;a++ ){
                String label=metaData.getColumnLabel(columnCount);
                Object op=rs.getObject(label);
                dataMap.put(label,op);
            }
            return new Result<Map<String,Object>>(dataMap);
        }catch (Exception e){
            return new Result(e);
        }
    };





    public  static  BiFunction<ResultSet,String[],Result<Object[]>> commonResultSetToObjects=(rs,columns) ->{
        try {
            Map<String,Object> dataMap=new HashMap();
            Object[] objs=new Object[columns.length];
            for(int a=0;a<=columns.length;a++ ){
                Object op=rs.getObject(columns[a]);
                objs[a]=op;
            }
            return new Result<Object[]>(objs);
        }catch (Exception e){
            return new Result(e);
        }
    };

    public static void register(Class<?> type, JdbcType jdbcType) {
        typeMap.put(type, jdbcType);
    }

    public static String[] fetchTableColumns(String sql,JdbcTemplate jdbcTemplate){
        SqlRowSet sqlRowSet=jdbcTemplate.queryForRowSet(sql);
        SqlRowSetMetaData sqlRowSetMetaData=sqlRowSet.getMetaData();
        int columnCount=sqlRowSetMetaData.getColumnCount();
        String[] columns=new String[columnCount];
        for(int a=1;a<=columnCount;a++){
            String columnLabel=sqlRowSetMetaData.getColumnLabel(a);
            columns[a-1]=columnLabel;
        }
        return columns;
    }




    private static String getPropName(String columnLabel){
         columnLabel=replaceBlank(columnLabel.toLowerCase());
         if(columnLabel.endsWith("_")) {
             columnLabel=columnLabel.substring(0,columnLabel.length()-1);
         }
         if(columnLabel.startsWith("_")){
             columnLabel=columnLabel.substring(1);
         }
         StringBuilder sb=new StringBuilder(columnLabel.toLowerCase());
         while(sb.indexOf("_")>-1){
               int l=sb.indexOf("_");
               sb.setCharAt(l+1,Character.toUpperCase(sb.charAt(l+1)));
               sb.deleteCharAt(l);
         }
         return sb.toString();
    }


    public static String replaceBlank(String str) {
        String dest = "";
        if (str!=null) {
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }

    public static void main(String[] args) {
    }

    public static MapSqlParameterSource newMapSqlParameterSource(Map<String,?> params, Map<String,Integer> propNameJdbcType){
        Map<String,Object> remaps = Maps.newHashMap();
        params.entrySet().forEach(s->{
            if(propNameJdbcType.containsKey(s.getKey())){
                remaps.put(s.getKey(),new SqlParameterValue(propNameJdbcType.get(s.getKey()),s.getValue()));
            }else{
                remaps.put(s.getKey(),s.getValue());
            }
        });
        return new MapSqlParameterSource(remaps);
    }
}
